package com.bjfu.college.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * ExcelUtils工具类
 *
 * @author xgy
 * @since 2023-02-13
 */
public class ExcelUtils {
    /**
     * 默认读取第一个sheet表
     * @param inputStream
     * @return
     * @throws IOException
     */
    public static List<Map<String,String>> excelToShopIdList(InputStream inputStream) throws IOException{
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = workbook.getSheetAt(0);
        List<Map<String,String>> list = new ArrayList<Map<String,String>>();
        Map<String,String> map = null;
        int totalRows = sheet.getPhysicalNumberOfRows();
        // 获取表头的总列数
        int totalCols = sheet.getRow(0).getPhysicalNumberOfCells();
        // 遍历行
        for (int i = 0; i < totalRows; i++) {
            // 遍历列
            map = new HashMap<String,String>();
            for (int j = 0; j < totalCols; j++) {
                // 获取 i 行 j 列
                if(sheet.getRow(i) == null)
                    continue;
                Cell cell = sheet.getRow(i).getCell(j);
                // 判断该列是否为 null
                if (cell == null || cell.getCellType() == CellType.BLANK) {
                    continue;
                }
                // 判断是否是字符类型
                if (cell.getCellType() == CellType.STRING) {
                    map.put("col"+j, cell.getStringCellValue());
                } else {
                    // 单元格为数值类型
                    cell.setCellType(CellType.NUMERIC);
                    // 处理精度丢失
                    map.put("col"+j, new BigDecimal(String.valueOf(cell.getNumericCellValue())).toPlainString());
                }
            }
            if (map.size()>0)
                list.add(map);
        }
        return list;
    }

    /**
     * @Description: 方案导出一个excel表
     * @Author: Pzf
     * @Date:   2023/7/18 0:21
     * @Param:  [response, titleKeyList, titleMap, src_list]
     * @Return: void
     */
    public static void exportDataExcel(HttpServletResponse response, ArrayList<String> titleKeyList, Map<String, String> titleMap, List<Map<String,Object>> src_list) throws IOException {

        //输出xls文件名称
        String xlsFile_name = System.currentTimeMillis() + ".xlsx";

        //内存中只创建100个对象
        Workbook wb = new SXSSFWorkbook(100);
        Sheet sheet = null;     //工作表对象
        Row nRow = null;        //行对象
        Cell nCell = null;      //列对象

        int rowNo = 0;      //总行号
        int pageRowNo = 0;  //页行号

        for (int k=0;k<src_list.size();k++) {
            Map<String,Object> srcMap = src_list.get(k);
            //写入300000条后切换到下个工作表
            if(rowNo%300000==0){
                //创建新的sheet对象
                wb.createSheet("工作簿"+(rowNo/300000));
                //动态指定当前的工作表
                sheet = wb.getSheetAt(rowNo/300000);
                //新建了工作表,重置工作表的行号为0
                pageRowNo = 0;
                // -----------定义表头-----------
                nRow = sheet.createRow(pageRowNo++);
                // 列数 titleKeyList.size()
                for(int i=0;i<titleKeyList.size();i++){
                    Cell cell_tem = nRow.createCell(i);
                    cell_tem.setCellValue(titleMap.get(titleKeyList.get(i)));
                }
                rowNo++;
                // ---------------------------
            }
            rowNo++;
            nRow = sheet.createRow(pageRowNo++);    //新建行对象

            // 行，获取cell值
            for(int j=0;j<titleKeyList.size();j++){
                nCell = nRow.createCell(j);
                if (srcMap.get(titleKeyList.get(j)) != null) {
                    nCell.setCellValue(srcMap.get(titleKeyList.get(j)).toString());
                } else {
                    nCell.setCellValue("");
                }
            }
        }
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + xlsFile_name);
        response.flushBuffer();
        OutputStream outputStream = response.getOutputStream();
        try {
            wb.write(response.getOutputStream());
            wb.close();
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
            throw new IOException(e.getMessage());
        }
    }
}
